package com.zhm.demo.duckdb;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.*;

public class DuckDbTest {

    public static void test1() throws Exception {
        Class.forName("org.duckdb.DuckDBDriver");
        Connection conn = DriverManager.getConnection("jdbc:duckdb:");
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(
                """
                            SELECT DATE_PART('day',TO_TIMESTAMP(销售订单日期 / 1000)) from 'D:\\cc.csv'
                        
                        """);
        while (rs.next()) {
            //　getString(String columnname)-------------根据列名获取本列的值；
            //getString(int index)--------------根据索引获取指定位置的值；
            System.out.println("id" + "\t" + rs.getString(1));
            System.out.println("name" + "\t" + rs.getString(2));
        }
    }

    public static void test2() throws Exception {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:duckdb:/duckdb/duckdb.db"); // 文件模式
        // 内存模式：jdbc:duckdb:memory:
        config.setDriverClassName("org.duckdb.DuckDBDriver");
        HikariDataSource dataSource = new HikariDataSource(config);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.execute("""
                    CREATE TABLE IF NOT EXISTS user  (
                        id INTEGER PRIMARY KEY,
                        name VARCHAR(50),
                        age INTEGER
                    )
                """);
        jdbcTemplate.update("INSERT INTO user VALUES (?, ?, ?)", 2, "张三", 28);
        jdbcTemplate.query("select * from user", rs -> {
            while (rs.next()) {
                System.out.println("id" + "\t" + rs.getString(1));
                System.out.println("name" + "\t" + rs.getString(2));
            }
        });
    }


    public static void test3() throws Exception {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:duckdb:/duckdb/duckdb.db"); // 文件模式
        // 内存模式：jdbc:duckdb:memory:
        config.setDriverClassName("org.duckdb.DuckDBDriver");
        HikariDataSource dataSource = new HikariDataSource(config);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.query("""
                 SELECT a.销售订单日期 as d, b.产品型号 as e from 'D:\\cc.csv' as a join 'D:\\kk.csv' as b on a.物料编码=b.物料编码
                """, rs -> {
            while (rs.next()) {
                System.out.println("d" + "\t" + rs.getString(1));
                System.out.println("e" + "\t" + rs.getString(2));
            }
        });
    }

    public static void test4() throws Exception {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:duckdb:/duckdb/duckdb.db"); // 文件模式
        // 内存模式：jdbc:duckdb:memory:
        config.setDriverClassName("org.duckdb.DuckDBDriver");
        HikariDataSource dataSource = new HikariDataSource(config);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.query("""
                 SELECT *
                 FROM read_json('D:\\a.json', records = true);
                """, rs -> {
            while (rs.next()) {
                System.out.println("d" + "\t" + rs.getString(1));
                System.out.println("e" + "\t" + rs.getString(2));
            }
        });
    }

    public static void test5() throws Exception {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:duckdb:/duckdb/duckdb.db"); // 文件模式
        // 内存模式：jdbc:duckdb:memory:
        config.setDriverClassName("org.duckdb.DuckDBDriver");
        HikariDataSource dataSource = new HikariDataSource(config);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.execute("""
           
                CREATE TABLE ods_device_sales (
                    device_id VARCHAR(20) COMMENT '设备唯一ID',
                    model VARCHAR(50) COMMENT '设备型号',
                    price DECIMAL(10,2) COMMENT '标价',
                    is_sold TINYINT COMMENT '是否成交(0/1)',
                    sale_time TIMESTAMP COMMENT '销售时间'
                ) PARTITIONED BY (dt STRING);
               
                CREATE TABLE dwd_device_sales_clean AS
                SELECT\s
                    model,
                    CAST(price AS DECIMAL(10,2)) AS clean_price,
                    CASE\s
                        WHEN is_sold IN (0,1) THEN is_sold\s
                        ELSE NULL\s
                    END AS is_sold,
                    DATE_FORMAT(sale_time, 'yyyy-MM-dd HH:mm:ss') AS sale_time
                FROM ods_device_sales
                WHERE dt BETWEEN '2023-01-01' AND '2023-12-31'
                  AND price BETWEEN 100 AND 100000  -- 过滤异常价格
                  AND model IS NOT NULL;
                """);
    }

    public static void main(String[] args) throws Exception {
        test2();
    }
}
